package com.bank.dao;

import com.bank.entity.Customer;
import com.bank.entity.Card;
import com.bank.entity.Transaction;
import com.bank.entity.DepositType;

import java.io.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.nio.charset.StandardCharsets;
import java.util.List;

public class CSVExporter {

    // ================== 数据库导出核心方法 ==================
    /**
     * 从数据库导出数据到CSV文件（解决Excel中文乱码问题）
     * @param url 数据库连接URL
     * @param user 数据库用户名
     * @param password 数据库密码
     * @param querySql 查询SQL
     * @param csvFilePath 输出CSV文件路径
     * @throws Exception 抛出异常
     */
    public static void exportFromDatabase(String url, String user, String password, String querySql, String csvFilePath) throws Exception {
        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(querySql);
             OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(csvFilePath), StandardCharsets.UTF_8)) {

            // 写入UTF-8 BOM，解决Excel中文乱码问题
            writer.write('\ufeff');

            ResultSetMetaData meta = rs.getMetaData();
            int columnCount = meta.getColumnCount();

            // 写表头
            for (int i = 1; i <= columnCount; i++) {
                writer.append(meta.getColumnName(i));
                if (i < columnCount) writer.append(",");
            }
            writer.append("\n");

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            while (rs.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    String data = "";
                    int columnType = meta.getColumnType(i);
                    String columnName = meta.getColumnName(i).toLowerCase();

                    // 根据列类型处理数据
                    switch (columnType) {
                        case Types.TIMESTAMP:
                        case Types.DATE:
                        case Types.TIME:
                            Timestamp ts = rs.getTimestamp(i);
                            if (ts != null) {
                                data = sdf.format(ts);
                            } else {
                                data = "";
                            }
                            break;
                        case Types.NUMERIC:
                        case Types.DECIMAL:
                        case Types.DOUBLE:
                        case Types.FLOAT:
                        case Types.REAL:
                            // 数值类型，保留两位小数
                            double numValue = rs.getDouble(i);
                            if (!rs.wasNull()) {
                                data = String.format("%.2f", numValue);
                            } else {
                                data = "";
                            }
                            break;
                        case Types.BIGINT:
                        case Types.INTEGER:
                        case Types.SMALLINT:
                        case Types.TINYINT:
                            // 整数类型
                            long intValue = rs.getLong(i);
                            if (!rs.wasNull()) {
                                data = String.valueOf(intValue);
                            } else {
                                data = "";
                            }
                            break;
                        default:
                            // 其他类型作为字符串处理
                            data = rs.getString(i);
                            if (data == null) {
                                data = "";
                            }
                    }

                    // 特殊字段处理：长数字字段加单引号，防止Excel科学计数法
                    if (columnName.contains("telephone") ||
                            columnName.contains("phone") ||
                            columnName.contains("pid") ||   // 身份证号
                            columnName.contains("idcard") ||
                            columnName.contains("cardid") ||
                            columnName.contains("customerid")) {

                        if (!data.isEmpty() && isLongNumeric(data)) {
                            data = "'" + data;
                        }
                    }
                    // 特殊字段处理：金额字段格式化为货币
                    else if (columnName.contains("money") || columnName.contains("balance")) {
                        if (!data.isEmpty() && isNumeric(data)) {
                            try {
                                double value = Double.parseDouble(data);
                                data = String.format("%.2f", value);
                            } catch (NumberFormatException e) {
                                // 保持原值
                            }
                        }
                    }

                    // CSV字段中如果有逗号或双引号，需要转义
                    if (data.contains(",") || data.contains("\"") || data.contains("\n") || data.contains("\r")) {
                        data = "\"" + data.replace("\"", "\"\"") + "\"";
                    }

                    writer.append(data);
                    if (i < columnCount) {
                        writer.append(",");
                    }
                }
                writer.append("\n");
            }

            System.out.println("数据库数据导出成功: " + csvFilePath);
        }
    }

    // ================== 特定表的导出方法 ==================
    public static void exportUserInfoFromDB(String url, String user, String password, String csvFilePath) throws Exception {
        String sql = "SELECT customerID, customerName, PID, telephone, address FROM userInfo";
        exportFromDatabase(url, user, password, sql, csvFilePath);
    }

    public static void exportCardInfoFromDB(String url, String user, String password, String csvFilePath) throws Exception {
        String sql = "SELECT cardID, curID, savingID, openDate, openMoney, balance, pass, IsReportLoss, customerID FROM cardInfo";
        exportFromDatabase(url, user, password, sql, csvFilePath);
    }

    public static void exportDepositInfoFromDB(String url, String user, String password, String csvFilePath) throws Exception {
        String sql = "SELECT savingID, savingName, descrip FROM deposit";
        exportFromDatabase(url, user, password, sql, csvFilePath);
    }

    public static void exportTradeInfoFromDB(String url, String user, String password, String csvFilePath) throws Exception {
        String sql = "SELECT tradeID, tradeDate, tradeType, cardID, tradeMoney, remark FROM tradeInfo";
        exportFromDatabase(url, user, password, sql, csvFilePath);
    }

    // ================== 实体对象导出方法（解决Excel中文乱码问题）==================
    public static void exportUsersToCSV(List<Customer> customers, String filename) {
        // 使用UTF-8编码并添加BOM头
        try (OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(filename), StandardCharsets.UTF_8)) {
            // 写入UTF-8 BOM，解决Excel中文乱码问题
            writer.write('\ufeff');

            // 写入表头
            writer.append("客户编号,客户姓名,身份证号,联系电话,联系地址\n");
            for (Customer customer : customers) {
                // 格式化身份证号（添加单引号）
                String pid = formatIDNumber(customer.getPID());
                // 格式化电话号码
                String telephone = formatTelephone(customer.getTelephone());
                // 处理空地址
                String address = customer.getAddress() != null ? customer.getAddress() : "";

                writer.append(String.format("%s,%s,%s,%s,%s\n",
                        customer.getCustomerID(),
                        customer.getCustomerName(),
                        pid,
                        telephone,
                        address));
            }
            writer.flush();
            System.out.println("用户数据已成功导出到: " + filename);
        } catch (IOException e) {
            System.err.println("导出用户数据到CSV时发生错误: " + e.getMessage());
        }
    }

    public static void exportCardsToCSV(List<Card> cards, String filename) {
        // 使用UTF-8编码并添加BOM头
        try (OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(filename), StandardCharsets.UTF_8)) {
            // 写入UTF-8 BOM，解决Excel中文乱码问题
            writer.write('\ufeff');

            writer.append("卡号,币种,开卡日期,开卡金额,账户余额,取款密码,是否挂失,客户编号,存款类型\n");
            for (Card card : cards) {
                // 格式化卡号（添加单引号）
                String cardId = formatLongNumber(card.getCardID());
                // 格式化客户编号
                String customerId = formatLongNumber(String.valueOf(card.getCustomerID()));

                writer.append(String.format("%s,%s,%s,%.2f,%.2f,%s,%s,%s,%d\n",
                        cardId,
                        card.getCurID(),
                        card.getOpenDate().toString(),
                        card.getOpenMoney(),
                        card.getBalance(),
                        card.getPassword(),
                        card.getIsReportLoss(),
                        customerId,
                        card.getSavingID()));
            }
            writer.flush();
            System.out.println("银行卡数据已成功导出到: " + filename);
        } catch (IOException e) {
            System.err.println("导出银行卡数据到CSV时发生错误: " + e.getMessage());
        }
    }

    public static void exportTransactionsToCSV(List<Transaction> transactions, String filename) {
        // 使用UTF-8编码并添加BOM头
        try (OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(filename), StandardCharsets.UTF_8)) {
            // 写入UTF-8 BOM，解决Excel中文乱码问题
            writer.write('\ufeff');

            writer.append("交易日期,交易类型,交易金额,卡号,备注\n");
            for (Transaction transaction : transactions) {
                // 格式化卡号（添加单引号）
                String cardId = formatLongNumber(transaction.getCardID());

                writer.append(String.format("%s,%s,%.2f,%s,%s\n",
                        transaction.getTradeDate(),
                        transaction.getTradeType(),
                        transaction.getTradeMoney(),
                        cardId,
                        transaction.getRemark()));
            }
            writer.flush();
            System.out.println("交易数据已成功导出到: " + filename);
        } catch (IOException e) {
            System.err.println("导出交易数据到CSV时发生错误: " + e.getMessage());
        }
    }

    public static void exportDepositTypesToCSV(List<DepositType> depositTypes, String filename) {
        // 使用UTF-8编码并添加BOM头
        try (OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(filename), StandardCharsets.UTF_8)) {
            // 写入UTF-8 BOM，解决Excel中文乱码问题
            writer.write('\ufeff');

            writer.append("业务类型编号,业务名称,描述\n");
            for (DepositType depositType : depositTypes) {
                writer.append(String.format("%d,%s,%s\n",
                        depositType.getSavingID(),
                        depositType.getSavingName(),
                        depositType.getDescript()));
            }
            writer.flush();
            System.out.println("存款业务数据已成功导出到: " + filename);
        } catch (IOException e) {
            System.err.println("导出存款业务数据到CSV时发生错误: " + e.getMessage());
        }
    }

    // ================== 辅助方法 ==================
    /**
     * 格式化身份证号（防止Excel科学计数法）
     */
    private static String formatIDNumber(String idNumber) {
        if (idNumber == null || idNumber.isEmpty()) {
            return "";
        }
        // 身份证号添加单引号前缀
        return "'" + idNumber;
    }

    /**
     * 格式化电话号码（防止Excel科学计数法）
     */
    private static String formatTelephone(String phone) {
        if (phone == null || phone.isEmpty()) {
            return "";
        }
        // 电话添加单引号前缀
        return "'" + phone;
    }

    /**
     * 通用长数字格式化（防止Excel科学计数法）
     */
    private static String formatLongNumber(String number) {
        if (number == null || number.isEmpty()) {
            return "";
        }
        // 长数字添加单引号前缀
        return "'" + number;
    }

    /**
     * 检查字符串是否为数值
     */
    private static boolean isNumeric(String str) {
        return str != null && str.matches("-?\\d+(\\.\\d+)?");
    }

    /**
     * 检查字符串是否为长整型数值
     */
    private static boolean isLongNumeric(String str) {
        return str != null && str.matches("\\d{10,}"); // 10位以上数字
    }

    // ================== 测试方法 ==================
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/bank_db?useSSL=false&serverTimezone=UTC";
        String user = "root";
        String password = "password";
        String userHome = System.getProperty("user.home");

        try {
            // 从数据库直接导出（解决中文乱码）
            exportUserInfoFromDB(url, user, password, userHome + "/Desktop/用户信息.csv");
            exportCardInfoFromDB(url, user, password, userHome + "/Desktop/银行卡信息.csv");
            exportDepositInfoFromDB(url, user, password, userHome + "/Desktop/存款类型.csv");
            exportTradeInfoFromDB(url, user, password, userHome + "/Desktop/交易记录.csv");

            System.out.println("数据库导出完成！");
        } catch (Exception e) {
            System.err.println("数据库导出失败: " + e.getMessage());
            e.printStackTrace();
        }
    }
}